package com.fduss.artStore.dao.impl;

import com.fduss.artStore.dao.GalleryDao;
import com.fduss.artStore.entity.Gallery;
import com.fduss.artStore.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by alex on 28/06/2017.
 */
public class GalleryDaoImpl implements GalleryDao{
    private JdbcUtil util;

    public GalleryDaoImpl(){
        util = new JdbcUtil();
    }


    @Override
    public Gallery add(Gallery gallery) {
        Connection conn = util.getConnection();
        String sql = "INSERT INTO `art`.`gallery` " +
                "(`GalleryID`, `GalleryName`, `GalleryNativeName`, `GalleryCity`, " +
                "`GalleryCountry`, `Latitude`, `Longitude`, `GalleryWebSite`) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, gallery.getGalleryId());
            pst.setString(2, gallery.getGalleryName());
            pst.setString(3, gallery.getGalleryNativeName());
            pst.setString(4, gallery.getGalleryCity());
            pst.setString(5, gallery.getGalleryCountry());
            pst.setDouble(6, gallery.getLatitude());
            pst.setDouble(7, gallery.getLongitude());
            pst.setString(8, gallery.getGalleryWebSite());

            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }

        return flag ? gallery : null;
    }

    @Override
    public void delete(int galleryId) {
        Connection conn = util.getConnection();
        String sql = "DELETE * FROM gallery WHERE GalleryID = ?";
        PreparedStatement pst = null;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, galleryId);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }
    }

    @Override
    public Gallery update(Gallery gallery) {
        
        Connection conn = util.getConnection();
        String sql = "UPDATE `art`.`gallery`" +
                " SET `GalleryName`=?, `GalleryNativeName`=?," +
                " `GalleryCity`=?, `GalleryCountry`=?, `Latitude`=?, `Longitude`=?" +
                " WHERE `GalleryID`=?";
        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setString(1, gallery.getGalleryName());
            pst.setString(2, gallery.getGalleryNativeName());
            pst.setString(3, gallery.getGalleryCity());
            pst.setString(4, gallery.getGalleryCountry());
            pst.setDouble(5, gallery.getLatitude());
            pst.setDouble(6, gallery.getLongitude());
            pst.setInt(7, gallery.getGalleryId());
            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }


        return flag ? gallery : null;
    }

    @Override
    public Gallery getOne(int galleryId) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM `art`.`gallery` WHERE `GalleryID` = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Gallery gallery = null;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, galleryId);
            rs = pst.executeQuery();

            if (rs.next()){
                gallery = buildGallery(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }


        return gallery;
    }

    @Override
    public List<Gallery> getAll() {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM `art`.`gallery`";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Gallery> gallery = new ArrayList<>();

        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();

            while (rs.next()){
                gallery.add(buildGallery(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }

        return gallery;
    }


    private Gallery buildGallery(ResultSet rs) throws SQLException {
        Gallery gallery = new Gallery();
        gallery.setGalleryCity(rs.getString("GalleryCity"));
        gallery.setGalleryCountry(rs.getString("GalleryCountry"));
        gallery.setGalleryId(rs.getInt("GalleryID"));
        gallery.setGalleryName(rs.getString("GalleryName"));
        gallery.setGalleryNativeName(rs.getString("GalleryNativeName"));
        gallery.setGalleryWebSite(rs.getString("GalleryWebSite"));
        gallery.setLatitude(rs.getDouble("Latitude"));
        gallery.setLongitude(rs.getDouble("Longitude"));
        return gallery;
    }
}
